#!/usr/bin/python
# coding:utf-8
# import MySQLdb
import constant.constant_value as constant
import mysql.connector

class Article_DB:
    cnn = None
    cursor = None
    def __init__(self):
        config={'host':constant.db_ip,
                'user':constant.db_username,
                'password':constant.db_password,
                'port':constant.db_port ,
                'database':constant.db_database_name,
                'charset':'gbk'
        }
        try:
            self.cnn=mysql.connector.connect(**config)
            self.cursor=self.cnn.cursor()
        except mysql.connector.Error as e:
            print('connect fails!{}'.format(e))
        
    def getCursor(self):
        self.__init__()
        return self.cursor;
    
    def closeConn(self):
        self.cnn.close()
        
    def closeCursor(self):
        self.cursor.close()
    
    def testDatabase(self):
        cursor = self.getCursor()
        cursor.execute("SELECT VERSION()")
        data = cursor.fetchone()
        print "Database version : %s " % data
    
    def get_article(self,table_name):
        sql = "select a.id,a.title,a.article from "+ table_name+" a where status = 0 limit 1"
        aid=""
        title= ""
        article=""
        try:
            cur =self.getCursor()
            cur.execute(constant.db_use_table_name)
            cur.execute(sql)
            for row in cur.fetchall():
                aid = row[0]
                title = row[1]
                article = row[2]
            cur = None;
            self.closeCursor()
            self.closeConn()
        except Exception as err:
            print(err);
            print u"查询失败";
        return aid,title,article;
    
    def update_article_status(self,table_name,status,cid):
        if id is not None:
            sql = "update "+table_name+" set status = "+str(status)+" where id ='"+str(cid)+"'"
            tag = False
            try:
                cur =self.getCursor()
                cur.execute(constant.db_use_table_name)
                cur.execute(sql)
                self.cnn.commit()
                self.closeCursor()
                self.closeConn()
                tag = True
                return tag
            except Exception as err:
                # Rollback in case there is any errorr 
                print(err);
                self.closeCursor()
                self.closeConn()
                tag = False
                return tag
            
    def get_count(self,table_name,status):
        if (status >=0):
            sql = "select count(1) from "+ table_name+" a where status = "+ str(status) +" limit 1"
        else:
            sql = "select count(1) from "+ table_name+" a where 1=1 limit 1"
        count=""
        try:
            cur =self.getCursor()
            cur.execute(constant.db_use_table_name)
            cur.execute(sql)
            for row in cur.fetchall():
                count = row[0]
            cur = None;
            self.closeCursor()
            self.closeConn()
        except Exception as err:
            print(err);
            print u"查询失败";
        return count;
    
if __name__ == "__main__":
    db = Article_DB()
    print db.get_article("other_888nongyewang_quanguoxinxilianbo")
    print db.get_count("other_888nongyewang_quanguoxinxilianbo",-1)